package DB;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;

abstract class freebaseFile{
	int[] selectedCols;
	String fileName;
	int seperateColmn;
	int numToTable;
	boolean giveRank;
	int updateCount;
	String[] lineTokens=null, linkTokens= null;
	public abstract String generateSqlLinker(int id,String s2);
	
	freebaseFile(int [] selectedCols1,String fileName1,int seperateColmn1, int numtotable, boolean giverank,int updatecount){
		selectedCols=selectedCols1;
		fileName=fileName1;
	    seperateColmn=seperateColmn1;
	    numToTable=numtotable;
	    giveRank=giverank;
	    updateCount=updatecount;
	}
}

public class DBImporter {
	public static int totalbytes,bytesreceived=0;
	public static String state;
	public static String currentFile;
	final static String freeBaseURL="http://download.freebase.com/datadumps/latest/browse/fictional_universe/";
	
	public static void makeIMPORT(){
		freebaseFile[] freebaseFiles=new freebaseFile[]{
				
			new freebaseFile(new int[]{1,2,4,5,9,24,25,26}, "fictional_character",-1,12,true,1000){//8+ 3 0f strength,durability..
				public String generateSqlLinker(int ID,String name){return null;}},
						
			new freebaseFile(new int[]{1,2,3}, "character_powers",3,6,true,100){//same here
				public String generateSqlLinker(int powerID,String characterName){
					return "INSERT INTO charactertopower(idCharacter,idPower) VALUES(" +
						"(SELECT id FROM fictional_character WHERE name='"+characterName+"' LIMIT 1),"+powerID+")";}},
							
			new freebaseFile(new int[]{1,2,3}, "character_species",3,3,false,100){
				public String generateSqlLinker(int speciesID,String characterName){
					return "INSERT INTO charactertospecies(idCharacter,idSpecies) VALUES(" +
						"(SELECT id FROM fictional_character WHERE name='"+characterName+"' LIMIT 1),"+speciesID+")";}},
					
			new freebaseFile(new int[]{1,2}, "fictional_setting",-1,3,false,100){
				public String generateSqlLinker(int ID,String name){return null;}},
						
			new freebaseFile(new int[]{1,2,5,10}, "fictional_object",5,7,true,100){
				public String generateSqlLinker(int objectID,String characterName){
					return "INSERT INTO charactertoobject(idCharacter,idObject) VALUES(" +
						"(SELECT id FROM fictional_character WHERE name='"+characterName+"' LIMIT 1),"+objectID+")";}},
				
			//new freebaseFile(new int[]{1,2,3}, "character_rank"),
		};
		for (freebaseFile file : freebaseFiles){
			currentFile=file.fileName;
			makeIMPORTSingle(file);
		}
		state="Done";
	}
	
	public static void makeIMPORTSingle(freebaseFile file){
		try {
			Connection conn=DBConnector.openConnection();
			URL url = new URL(freeBaseURL+file.fileName+".tsv");
			HttpURLConnection myConnection = (HttpURLConnection) url.openConnection();
			bytesreceived=0;
			PreparedStatement pstmt=null;
			int id,batchsize=0;
			file.lineTokens=new String[file.numToTable];
			myConnection.setRequestMethod("GET");
			myConnection.connect();
			String tableLine;
			int answer = myConnection.getResponseCode();
			if (answer == HttpURLConnection.HTTP_OK){//URL is OK
				totalbytes=myConnection.getContentLength();
				byte[] data=new byte[totalbytes];
				InputStream inpstr=myConnection.getInputStream();
				state="Downloading";
				while (bytesreceived<totalbytes)
					bytesreceived+=inpstr.read(data, bytesreceived, totalbytes-bytesreceived);
				inpstr.close();
				
				BufferedReader inputstr=new BufferedReader(new InputStreamReader(new ByteArrayInputStream(data)));
				bytesreceived=0;
				state="Updating";
				Statement stmt=conn.createStatement();
				//To find out the name of the columns
				ResultSet rs= stmt.executeQuery("SELECT * FROM "+file.fileName+ " WHERE false");
				do{
					//clean array
					for (int j=0;j<file.lineTokens.length;j++)
						file.lineTokens[j]="";
					tableLine=inputstr.readLine();
					if (tableLine==null) break;
					if (!tableLine.replaceAll(" ", "").equals("")){//not blank line
						id=parseLine(conn,file,tableLine,batchsize);
						if (pstmt==null)
							pstmt=conn.prepareStatement(constructCommand(file.lineTokens,file.fileName,conn,rs));
						else
							pstmt.addBatch(constructCommand(file.lineTokens,file.fileName,conn,rs));
						batchsize++;
						if (batchsize==file.updateCount && pstmt!=null){
							pstmt.executeBatch();
							pstmt.clearBatch();
							batchsize=0;
						}
					}
					bytesreceived+=tableLine.length();
				}
				while(tableLine!=null);
				inputstr.close();
				
				if (pstmt!=null){
					pstmt.executeBatch();
					pstmt.clearBatch();
				}
				
				//make linking
				if (file.seperateColmn!=-1){ //if the table has links
					state="Linking";
					bytesreceived=0;
					inputstr=new BufferedReader(new InputStreamReader(new ByteArrayInputStream(data)));
					tableLine=inputstr.readLine();//header not interesting
					do{
						tableLine=inputstr.readLine();
						if (tableLine==null) break;
						treatLinks(file,tableLine);
						if (file.linkTokens!=null){
							String[] tokana=tableLine.split("	");
							String freeBaseID=getfreeBaseID(tokana);
							id=getIDForLinknig(conn,file,freeBaseID);
							for (String s:file.linkTokens){
								if (s!=null){
									//pstmt.addBatch(file.generateSqlLinker(id, s.replace("'","\\\'")));
									//System.out.println(file.generateSqlLinker(id, s.replace("'","\\\'")));
									try{
										pstmt.execute(file.generateSqlLinker(id, s.replace("'","\\\'")));
									}
									catch (Exception e) {continue;} //some rows that doesn't exist
								}
							}	
							//pstmt.executeBatch();
							//pstmt.clearBatch();
						}
						file.linkTokens=null;
						file.lineTokens[file.seperateColmn-1]="";
						bytesreceived+=tableLine.length();
					}
					while(tableLine!=null);
					pstmt.executeBatch();
					pstmt.clearBatch();
				}
				if (pstmt!=null)
					pstmt.close();
				DBConnector.closeConnection(conn);
			}
		} 
		catch (Exception e) {e.printStackTrace();} 
	}
	
	public static void treatLinks(freebaseFile file,String strLine){
		String[] tokana=strLine.split("	");
		if (file.seperateColmn-1>=tokana.length)
			return;
		String chosenString=tokana[file.seperateColmn-1];
		if (chosenString.contains(", "))
			chosenString=chosenString.replace(", ","  ");
		StringTokenizer st2 = new StringTokenizer(chosenString, ",");
		if (st2.countTokens()==0)
			return;
		file.linkTokens=new String[st2.countTokens()+1];
		int j=0;
		while (st2.hasMoreTokens()){
			String nextToken=st2.nextToken().replace("  ", ", ");
			if (!isDuplicated(nextToken,file.linkTokens))
				file.linkTokens[j]=nextToken;
			else
				file.linkTokens[j]=null;
			j++;
		}
	}
	
	public static boolean isDuplicated(String token,String[] linkTokens){
		for (String st:linkTokens)
			if (token!=null && st!=null && st.toLowerCase().equals(token.toLowerCase())) return true;
		return false;
	}
	
	public static int getIDForLinknig(Connection conn,freebaseFile file,String freeBaseID) throws SQLException{
		int id=-1;
		Statement stmt=conn.createStatement();
		ResultSet rs= stmt.executeQuery("SELECT id FROM "+file.fileName+" WHERE freeBaseID='"+freeBaseID+"'");
		rs.next();
		id=rs.getInt(1);
		return id;
	}
	
	static String getfreeBaseID(String[] lineTokens){
		int i;
		for (i=0;i<lineTokens.length;i++)
			if (lineTokens[i].startsWith("/m/")) break;
		if (i<lineTokens.length)
			return lineTokens[i];
		else 
			return null;
	}
	
	static int[] calcRanks(String line){//calculates Strength,Durability,Dexterity
		int[] ranks=new int[]{0,0,0};
		int length=line.split("	").length;	
		ranks[0]=Math.round(length*(float)Math.random());
		ranks[1]=Math.round((length-ranks[0])*(float)Math.random());
		ranks[2]=length-(ranks[0]+ranks[1]);
		return ranks;
	}
	
	static int parseLine(Connection conn,freebaseFile file,String strLine,int iteration){
		int  tokenNumber = 0,id=0,i=0;
		boolean gotFreebaseID=false;
		String[] tokana=strLine.split("	");
		while(tokenNumber<tokana.length && i<file.selectedCols.length){	
			String token =	tokana[tokenNumber];
			if (tokenNumber==file.selectedCols[i]-1 && tokenNumber!=file.seperateColmn-1){
				if (token.startsWith("/m/") && !gotFreebaseID){
					gotFreebaseID=true;
				//	id=GetGeneratedID(token,conn);
					file.lineTokens[i]=token;
				}
				else
					file.lineTokens[i]=token;
				i++;
			}
			tokenNumber++;
		}
		if (file.giveRank){ // put strength durability..
			int[] temp=calcRanks(strLine);
			for (int j=0;j<temp.length;j++)
				file.lineTokens[file.lineTokens.length-2-j]=Integer.toString(temp[j]);
		}
		file.lineTokens[file.lineTokens.length-1]="0";
		return id;
	}
	
	public static int GetGeneratedID(String freeBaseID,Connection conn){
		Statement stmt;
		ResultSet rs;
		int	id=-1;
		
		try{
			stmt=conn.createStatement();
			/*rs= stmt.executeQuery("SELECT idids,changed FROM ids WHERE freeBase_ids='"+freeBaseID+"'");
			if (rs.next()){
				//if (rs.getBoolean("changed")) //TODO: make update when change is not made but without getting duplicates(think of appropriate catch)
				id =rs.getInt("idids");
			}
			else{*/
				stmt.executeUpdate("INSERT INTO ids(freeBase_ids) VALUES('"+freeBaseID+"')",
									new String[]{"ids"});
				rs=stmt.getGeneratedKeys();
		        rs.next();
		        id=rs.getInt(1);
			//}
			//System.out.println("Success - GetGeneratedID, the generated ID is: " + id);
			stmt.close();
		}
		catch (SQLException e){
			System.out.println("ERROR executeUpdate - " + e.toString());
			java.lang.System.exit(0); 
		}
		return 0;
	}
	
	static String constructCommand(String[] tokens,String table,Connection conn,ResultSet rs){
		int i;
		String command = new String();
		try{
			int numOfAttr=rs.getMetaData().getColumnCount();
			Statement stmt=conn.createStatement();
			String[] attributes =new String[numOfAttr];
			for (i=2;i<=numOfAttr;i++)//don't need the id so i=2
				attributes[i-1]=rs.getMetaData().getColumnName(i);
			command+="INSERT INTO "+table+"(";
			for (i=2;i<=numOfAttr;i++){
				command+=attributes[i-1];
				if (i==numOfAttr)
					command+=")";
				else 
					command+=",";
			}
			command+="VALUES('";
			for (i=1;i<=tokens.length;i++){
				if (tokens[i-1]!=null)
					command+=tokens[i-1].replace("'","\\\'");
				else 
					command+=tokens[i-1];
				if (i<tokens.length)
					command+="','";
				else command+="')";
			}
			stmt.close();
		}
		catch (SQLException e){
			System.out.println("ERROR executeUpdate - " + e.toString());
			java.lang.System.exit(0); 
		}
		return command;
	}
}